﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Globalization;

namespace EWS
{
    class DBConnection
    {
        private static DBConnection instance;
        private MySqlConnection connection;
        private const string connectionString = "SERVER=ews2011.zapto.org;" +
                            "DATABASE=ews;" +
                            "UID=ews;" +
                            "PASSWORD=keSv69UpL3cXUyBS343s;";

        // singleton
        private DBConnection()
        {
            connection = new MySqlConnection(connectionString);
        }

        // singleton
        static public DBConnection getInstance()
        {
            if (instance == null)
                instance = new DBConnection();
            return instance;
        }

        /*
         * gibt eine nach der Bettnummer sortierte Liste mit all den Betten der DB zurück,
         * deren letzter/neuster Timestamp nicht älter ist
         * als eine in der Funktion definierte Zeit (maxAgeMinutes)
         */
        public SortedList<int, Bed> getBedsByWardNumber(int wardNumber)
        {
            SortedList<int, Bed> bedsList = new SortedList<int, Bed>();
            
            string selectString = "SELECT m.BedNumber, m.BloodPressureDia, m.BloodPressureSys," +
                " m.Pulse, m.ResperatoryFreq, m.SPO2, m.Temperature, m.CreationTime," +
                " d.Name, n.Name FROM MeasureData m" +
                " JOIN (SELECT BedNumber, MAX(CreationTime) AS date FROM MeasureData" +
                " WHERE BedNumber LIKE '" + wardNumber + "%' GROUP BY BedNumber) lastEntry" +
                " ON m.BedNumber = lastEntry.BedNumber" +
                " AND m.CreationTime = lastEntry.date" +
                " INNER JOIN Doctor d"+ 
                " ON m.FKDoctorNumber = d.PKDoctorNumber" +
                " INNER JOIN Nurse n" + 
                " ON m.FKNurseNumber = n.PKNurseNumber";

            open();
            MySqlDataReader reader = executeSelect(selectString);

            while (reader.Read())
            {
                double maxAgeMinutes = 60;
                DateTime creationTime = reader.GetDateTime(7);
                bool isRecent = creationTime.AddMinutes(maxAgeMinutes).CompareTo(DateTime.Now) > 0;

                if (isRecent)
                {
                    int bedNumber = reader.GetInt32(0);
                    int bpDia = reader.GetInt32(1);
                    int bpSys = reader.GetInt32(2);
                    int respereatoryFreq = reader.GetInt32(3);
                    int pulse = reader.GetInt32(4);
                    int oxygenSaturation = reader.GetInt32(5);
                    float temperature = reader.GetFloat(6);
                    // collumn 7 is timestamp
                    string doctor = reader.GetString(8);
                    string nurse = reader.GetString(9);

                    bpdata bloodData = new bpdata(bpDia, bpSys);
                    measureData mData = new measureData(bedNumber, bloodData, pulse, respereatoryFreq, temperature, oxygenSaturation, creationTime);

                    Bed bedItem = new Bed(mData, doctor, nurse);
                    bedsList.Add(bedNumber, bedItem);
                }
            }

            reader.Close();
            close();

            return bedsList;
        }

        public bool insertMeasureData(measureData paramMeasureData, int pkdoc, int pknurse)
        {
            if ((paramMeasureData.BedNumber > 0) && (pkdoc > 0) && (pknurse > 0))
            {
                IFormatProvider format = new CultureInfo("en-US");

                string insertString = "INSERT INTO MeasureData (BedNumber, Temperature,"
                        + " BloodPressureSys, BloodPressureDia, ResperatoryFreq, SPO2,"
                        + " Pulse, FKNurseNumber, FKDoctorNumber) VALUES ("
                        + paramMeasureData.BedNumber + ", "
                        + paramMeasureData.Temperature.ToString(format) + ", "
                        + paramMeasureData.bpdata.Sys + ", "
                        + paramMeasureData.bpdata.Dia + ", "
                        + paramMeasureData.ResperatoryRate + ", "
                        + paramMeasureData.OxygenSaturation + ", "
                        + paramMeasureData.Pulse + ", "
                        + pknurse + ", "
                        + pkdoc + ")";
                open();
                executeInsert(insertString);
                close();
                return true;
            }
            return false;
        }

        public bool insertNurse(String nurseName)
        {
            if (nurseName != null && (!nurseName.Equals("")))
            {
                string insertString = "INSERT INTO Nurse (Name) VALUES ('" + nurseName + "')";
                open();
                executeInsert(insertString);
                close();
                return true;
            }
            return false;
        }

        public bool insertDoc(String docName)
        {
            if (docName != null && (!docName.Equals("")))
            {
                string insertString = "INSERT INTO Doctor (Name) VALUES ('" + docName + "')";
                open();
                executeInsert(insertString);
                close();
                return true;
            }
            return false;
        }

        public List<KeyValuePair<int, string>> getDoctors()
        {
            List<KeyValuePair<int, string>> doctorsList = new List<KeyValuePair<int, string>>();

            string selectString =
                        "SELECT PKDoctorNumber, Name " +
                        "FROM Doctor";

            open();

            MySqlDataReader reader = executeSelect(selectString);

            while (reader.Read())
            {
                int pkDoctor = reader.GetInt32(0);
                string doctorName = reader.GetString(1);

                KeyValuePair<int, string> entry = new KeyValuePair<int, string>(pkDoctor, doctorName);
                doctorsList.Add(entry);
            }

            reader.Close();
            close();

            return doctorsList;
        }

        public List<KeyValuePair<int, string>> getNurses()
        {
            List<KeyValuePair<int, string>> nursesList = new List<KeyValuePair<int, string>>();

            string selectString =
                        "SELECT PKNurseNumber, Name " +
                        "FROM Nurse";

            open();

            MySqlDataReader reader = executeSelect(selectString);

            while (reader.Read())
            {
                int pkNurse = reader.GetInt32(0);
                string nurseName = reader.GetString(1);

                KeyValuePair<int, string> entry = new KeyValuePair<int, string>(pkNurse, nurseName);
                nursesList.Add(entry);
            }

            reader.Close();
            close();

            return nursesList;
        }

        private MySqlDataReader executeSelect(string selectCommand)
        {
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = selectCommand;
            MySqlDataReader reader = command.ExecuteReader();
            return reader;
        }

        private void executeInsert(string insertCommand)
        {
            MySqlCommand command;
            
            command = connection.CreateCommand();
            command.CommandText = insertCommand;

            command.ExecuteNonQuery();
        }

        public void open()
        {
            if (!connection.Ping())
            {
                connection.Open();
            }
        }

        public void close()
        {
            if (connection.Ping())
            {
                connection.Close();
            }
        }
    }
}
